using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace NUnitHelper.Managers
{
    public class DataManagerTemplate
    {
        private static string _connectionString = "";
        private static string ConnectionString
        {
            get
            {
                return _connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }

        public static List<string> GetListOfTables(string dbFileName)
        {
            List<string> TableList = new List<string>();
            ConnectionString = String.Format(@"Data Source=.\SQLEXPRESS;AttachDbFilename=""{0}"";Integrated Security=True;User Instance=True", dbFileName);
            string sqlQuery = "SELECT so.name FROM sysobjects so WHERE so.xtype = 'U'";

            SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, ConnectionString);
            DataTable table = new DataTable();
            adapter.Fill(table);

            foreach (DataRow row in table.Rows)
            {
                TableList.Add(row["Name"].ToString());
            }

            return TableList;
        }

        private static string _classTable;
        private static string ClassTable
        {
            get
            {
                return _classTable;
            }
            set
            {
                string pluralTableName = value.Replace(" ", "");
                if (!pluralTableName.EndsWith("s"))
                {
                    if (pluralTableName.EndsWith("y"))
                    {
                        pluralTableName = pluralTableName.Substring(0, pluralTableName.Length - 1) + "ies";
                    }
                    else
                    {
                        pluralTableName = pluralTableName.Substring(0, pluralTableName.Length - 1) + "s";
                    }
                }
                _classTable = pluralTableName;
            }
        }

        private static string ClassRow
        {
            get
            {
                string singleTableName = ClassTable;
                if (ClassTable.EndsWith("ies"))
                {
                    singleTableName = singleTableName.Substring(0, singleTableName.Length - 3) + "y";
                }
                else if (ClassTable.EndsWith("s"))
                {
                    singleTableName = singleTableName.Substring(0, singleTableName.Length - 1);
                }
                return singleTableName;
            }
        }

        private static List<AwesomeDataColumn> TableColumns = new List<AwesomeDataColumn>();
        
        public static string GetDataManagerClass(string table)
        {
            ClassTable = table;

            string sqlQuery = @"SELECT " +
                                @"	c.name AS ColumnName, " +
                                @"	c.is_nullable AS Nullable, " +
                                @"	t.name as TypeName " +
                                @"FROM sys.columns AS c INNER JOIN sys.types AS t ON (c.user_type_id = t.user_type_id) " +
                                @"WHERE " +
                                @"	OBJECT_NAME(c.object_id) = @table";


            SqlCommand comm = new SqlCommand(sqlQuery, new SqlConnection(ConnectionString));
            comm.Parameters.AddWithValue("@table", table);

            DataTable datatable = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(comm);

            adapter.Fill(datatable);

            TableColumns.Clear();
            foreach (DataRow row in datatable.Rows)
            {
                if (!row["ColumnName"].ToString().Equals("ID", StringComparison.OrdinalIgnoreCase))
                {
                    TableColumns.Add(new AwesomeDataColumn(row["ColumnName"].ToString(),
                            Convert.ToBoolean(row["Nullable"]),
                            row["TypeName"].ToString()));
                }
            }

            string classTemplate = GetClassTemplate();
            classTemplate = classTemplate.Replace("[copytoitem]", CopyToItemString());
            classTemplate = classTemplate.Replace("[copyfromitem]", CopyFromItemString());
            classTemplate = classTemplate.Replace("[classrow]", ClassRowString());
            classTemplate = classTemplate.Replace("[plural]", ClassTable);
            classTemplate = classTemplate.Replace("[singular]", ClassRow);

            return MakeTabs(classTemplate);
        }

        private static string MakeTabs(string s)
        {
            StringBuilder sb = new StringBuilder();
            System.IO.StringReader reader = new System.IO.StringReader(s);
            int stack = 0;
            const int numSpaces = 4;

            while (reader.Peek() > 0)
            {
                string line = reader.ReadLine().Trim();
                if (line.Equals("{"))
                {
                    sb.Append(' ', stack * numSpaces);
                    sb.AppendLine(line);
                    stack += 1;
                }
                else if (line.Equals("}"))
                {
                    stack -= 1;
                    sb.Append(' ', stack * numSpaces);
                    sb.AppendLine(line);
                }
                else
                {
                    sb.Append(' ', stack * numSpaces);
                    sb.AppendLine(line);
                }
            }

            return sb.ToString();
        }

        private static string GetClassTemplate()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Configuration;");
            sb.AppendLine("using System.Data;");
            sb.AppendLine("namespace DAL");
            sb.AppendLine();
            sb.AppendLine("{");
            sb.AppendLine("    public class [plural] : DAL.DataManager<[singular]>");
            sb.AppendLine("    {");
            sb.AppendLine("        private static [plural] Instance = new [plural]();");
            sb.AppendLine("        static [plural]() {}");
            sb.AppendLine();
            sb.AppendLine("        public override string TableName");
            sb.AppendLine("        {");
            sb.AppendLine("            get { return @\"[plural]\"; }");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        protected override void CopyToItem(DataRow Row, [singular] Item)");
            sb.AppendLine("        {");
            sb.AppendLine("        [copytoitem]");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        protected override void CopyFromItem([singular] Item)");
            sb.AppendLine("        {");
            sb.AppendLine("        [copyfromitem]");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        public static [singular] Item(Int64 id)");
            sb.AppendLine("        {");
            sb.AppendLine("            return Instance.GetItem(id);");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        public static List<[singular]> All()");
            sb.AppendLine("        {");
            sb.AppendLine("            return Instance.GetItems();");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        public static void Add([singular] Item)");
            sb.AppendLine("        {");
            sb.AppendLine("            Instance.Insert(Item);");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        public static void Save([singular] Item)");
            sb.AppendLine("        {");
            sb.AppendLine("            Instance.Update(Item);");
            sb.AppendLine("        }");
            sb.AppendLine("    }");
            sb.AppendLine();
            sb.AppendLine("    public class [singular] : DAL.DataManagerRow");
            sb.AppendLine("    {");
            sb.AppendLine("    [classrow]");
            sb.AppendLine("    }");
            sb.AppendLine("}");
            return sb.ToString();
        }

        private static string CopyToItemString()
        {
            StringBuilder sb = new StringBuilder();
            foreach (AwesomeDataColumn col in TableColumns)
            {
                sb.AppendLine(col.CopyToItemString);
                sb.AppendLine();
            }

            return sb.ToString().Trim();
        }

        private static string CopyFromItemString()
        {
            StringBuilder sb = new StringBuilder();
            foreach (AwesomeDataColumn col in TableColumns)
            {
                sb.AppendLine(col.CopyFromItemString);
                sb.AppendLine();
            }
            return sb.ToString().Trim();
        }

        private static string ClassRowString()
        {
            StringBuilder sb = new StringBuilder();
            foreach (AwesomeDataColumn col in TableColumns)
            {
                sb.AppendLine(col.ClassRowString);
                sb.AppendLine();
            }
            return sb.ToString().Trim();
        }
    }


    public class AwesomeDataColumn
    {
        public AwesomeDataColumn(string columnName, bool isNullable, string sqlDataType)
        {
            this.Name = columnName;
            if (Name.EndsWith("ID", StringComparison.OrdinalIgnoreCase))
            {
                Name = Name.Substring(0, Name.Length - 2);
                IsObject = true;
            }
            this.Nullable = isNullable;
            this.SqlDataType = sqlDataType;
        }

        private bool _nullable;
        public bool Nullable
        {
            get
            {
                return _nullable;
            }
            set
            {
                _nullable = value;
            }
        }

        private string _name;
        public string Name
        {
            get
            {
                return _name;
            }
            set
            {
                _name = value;
            }
        }

        private string _sqlDataType;
        public string SqlDataType
        {
            get
            {
                return _sqlDataType;
            }
            set
            {
                _sqlDataType = value;
            }
        }

        private bool _isObject;
        public bool IsObject
        {
            get
            {
                return _isObject;
            }
            set
            {
                _isObject = value;
            }
        }

        public string CSharpDataType
        {
            get
            {
                string dataType = "object";
                if (!IsObject)
                {
                    switch (SqlDataType)
                    {

                        case "int":
                            dataType = "Int32";
                            break;
                        case "bigint":
                            dataType = "Int64";
                            break;
                        case "varchar":
                        case "char":
                        case "text":
                            dataType = "string";
                            break;
                        case "datetime":
                        case "smalldatetime":
                            dataType = "DateTime";
                            break;
                        case "float":
                        case "real":
                        case "decimal":
                            dataType = "double";
                            break;
                        case "bit":
                            dataType = "bool";
                            break;
                    }

                    if (Nullable && !dataType.Equals("string"))
                    {
                        dataType = String.Format("Nullable<{0}>", dataType);
                    }
                }
                else
                {
                    dataType = Name;
                }

                return dataType;
            }
        }

        public string CSharpConvertFunction
        {
            get
            {
                if (!IsObject)
                {
                    string function = CSharpDataType;
                    if (function.Equals("int"))
                    {
                        function = "Int32";
                    }
                    function = function.Replace("Nullable<", "");
                    function = function.Replace(">", "");
                    function = string.Format("{0}{1}", function[0].ToString().ToUpper(), function.Substring(1, function.Length - 1));
                    return String.Format("To{0}", function);
                }
                else
                {
                    return "";
                }
            }
        }

        public string ObjectPlural
        {
            get
            {
                string pluralTableName = Name.Replace(" ", "");
                if (!pluralTableName.EndsWith("s"))
                {
                    if (pluralTableName.EndsWith("y"))
                    {
                        pluralTableName = pluralTableName.Substring(0, pluralTableName.Length - 1) + "ies";
                    }
                    else
                    {
                        pluralTableName = String.Format("{0}s", pluralTableName);
                    }
                }
                return pluralTableName;
            }
        }

        private string Format(string s)
        {
            s = s.Replace("[colname]", Name);
            s = s.Replace("[datatype]", CSharpDataType);
            s = s.Replace("[convertfunction]", CSharpConvertFunction);
            s = s.Replace("[objectplural]", ObjectPlural);
            return s;
        }
        
        public string CopyToItemString
        {
            get
            {
                StringBuilder sb = new StringBuilder();
                if (Nullable)
                {
                    if (IsObject)
                    {
                        sb.AppendLine("    if (Row[\"[colname]\"] != System.DBNull.Value)");
                        sb.AppendLine("    {");
                        sb.AppendLine("        Item.[colname] = [objectplural].Item(Convert.ToInt64(Row[\"[colname]ID\"]));");
                        sb.AppendLine("    }");
                        sb.AppendLine("    else");
                        sb.AppendLine("    {");
                        sb.AppendLine("        Item.[colname] = null;");
                        sb.AppendLine("    }");
                    }
                    else
                    {
                        sb.AppendLine("    if (Row[\"[colname]\"] != System.DBNull.Value)");
                        sb.AppendLine("    {");
                        sb.AppendLine("        Item.[colname] = Convert.[convertfunction](Row[\"[colname]\"]);");
                        sb.AppendLine("    }");
                        sb.AppendLine("    else");
                        sb.AppendLine("    {");
                        sb.AppendLine("        Item.[colname] = null;");
                        sb.AppendLine("    }");
                    }
                }
                else
                {
                    if (IsObject)
                    {
                        sb.AppendLine("        Item.[colname] = [objectplural].Item(Convert.ToInt64(Row[\"[colname]ID\"]));");
                    }
                    else
                    {
                        sb.AppendLine("        Item.[colname] = Convert.[convertfunction](Row[\"[colname]\"]);");
                    }
                }

                return Format(sb.ToString());
            }
        }

        public string CopyFromItemString
        {
            get
            {
                StringBuilder sb = new StringBuilder();
                if (Nullable)
                {
                    if (IsObject)
                    {
                        sb.AppendLine("    if (Item.[colname] != null)");
                        sb.AppendLine("    {");
                        sb.AppendLine("        AddParam(\"[colname]ID\", Item.[colname].ID);");
                        sb.AppendLine("    }");
                        sb.AppendLine("    else");
                        sb.AppendLine("    {");
                        sb.AppendLine("        AddParam(\"[colname]ID\", System.DBNull.Value);");
                        sb.AppendLine("    }");
                    }
                    else
                    {
                        if (CSharpDataType.Equals("string"))
                        {
                            sb.AppendLine("    if (Item.[colname] != null)");
                            sb.AppendLine("    {");
                            sb.AppendLine("        AddParam(\"[colname]\", Item.[colname]);");
                            sb.AppendLine("    }");
                            sb.AppendLine("    else");
                            sb.AppendLine("    {");
                            sb.AppendLine("        AddParam(\"[colname]\", System.DBNull.Value);");
                            sb.AppendLine("    }");
                        }
                        else
                        {
                            sb.AppendLine("    if (Item.[colname].HasValue)");
                            sb.AppendLine("    {");
                            sb.AppendLine("        AddParam(\"[colname]\", Item.[colname].Value);");
                            sb.AppendLine("    }");
                            sb.AppendLine("    else");
                            sb.AppendLine("    {");
                            sb.AppendLine("        AddParam(\"[colname]\", System.DBNull.Value);");
                            sb.AppendLine("    }");
                        }
                    }
                }
                else
                {
                    if (IsObject)
                    {
                        sb.AppendLine("        AddParam(\"[colname]ID\", Item.[colname].ID);");
                    }
                    else
                    {
                        sb.AppendLine("        AddParam(\"[colname]\", Item.[colname]);");
                    }
                }

                return Format(sb.ToString());
            }
        }

        public string ClassRowString
        {
            get
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("        private [datatype] _[colname];");
                sb.AppendLine("        public [datatype] [colname]");
                sb.AppendLine("        {");
                sb.AppendLine("            get");
                sb.AppendLine("            {");
                sb.AppendLine("                return _[colname];");
                sb.AppendLine("            }");
                sb.AppendLine("            set");
                sb.AppendLine("            {");
                sb.AppendLine("                _[colname] = value;");
                sb.AppendLine("            }");
                sb.AppendLine("        }");

                return Format(sb.ToString());
            }
        }
    }
}
